<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Constraints in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, database, constraints, NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE">
<meta name="description" content="In this part of the SQLite tutorial, we will 
mention SQLite constraints.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Constraints</h1>


<p>
In this part of the SQLite tutorial, we will work with constraints.
</p>

<p>
<b>Constraints</b> are placed on columns. They limit the data, that can be
inserted into tables. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
We have the following constraints:
</p>

<ul>
    <li>NOT NULL</li>
    <li>UNIQUE</li>
    <li>PRIMARY KEY</li>
    <li>FOREIGN KEY</li>
    <li>CHECK</li>
    <li>DEFAULT</li>
</ul>


<h2>NOT NULL constraint</h2>

<p>
A column with a NOT NULL constraint, cannot have NULL values. 
</p>


<pre class="code">
sqlite> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL, 
   ...> FirstName TEXT NOT NULL, City TEXT);
</pre>

<p>
We create two columns with NOT NULL constraints.  
</p>

<pre class="code">
sqlite> INSERT INTO People VALUES(1, 'Hanks', 'Robert', 'New York');
sqlite> INSERT INTO People VALUES(2, NULL, 'Marianne', 'Chicago');
Error: People.LastName may not be NULL
</pre>

<p>
The first INSERT statement is executed OK, the second one fails. The error 
says that the LastName column may not be NULL. 
</p>


<h2>UNIQUE constraint</h2>

<p>
The UNIQUE constraint ensures, that all data are unique in a column. 
</p>

<pre class="code">
sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);
</pre>

<p>
Here we create a table Brands. The BrandName column is set to be UNIQUE.
There cannot be two brands with a same name. 
</p>

<pre class="code">
sqlite> INSERT INTO Brands VALUES(1, 'Coca Cola');
sqlite> INSERT INTO Brands VALUES(2, 'Pepsi');
sqlite> INSERT INTO Brands VALUES(3, 'Pepsi');
Error: column BrandName is not unique
</pre>

<p>
We get an error 'column BrandName is not unique'. There can only be one Pepsi brand. 
</p>

<p>
Note that a PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
</p>


<h2>Primary key</h2>

<p>
The PRIMARY KEY constraint uniquely identifies each record in a 
database table. There can be more UNIQUE columns, but only one primary 
key in a table. Primary keys are important when designing the database tables. 
Primary keys are unique ids. We use them to refer to table rows. Primary keys 
become foreign keys in other tables, when creating relations among
tables. Due to to a 'long-standing coding oversight', primary keys can be NULL in SQLite. This
is not the case with other databases. 
</p>

<pre class="code">
sqlite> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName TEXT);
</pre>

<p>
The Id column of the Brands table becomes a PRIMARY KEY. 
</p>


<pre class="code">
sqlite> INSERT INTO Brands(BrandName) VALUES('Coca Cola');
sqlite> INSERT INTO Brands(BrandName) VALUES('Pepsi');
sqlite> INSERT INTO Brands(BrandName) VALUES('Sun');
sqlite> INSERT INTO Brands(BrandName) VALUES('Oracle');
sqlite> SELECT * FROM Brands;
Id          BrandName 
----------  ----------
1           Coca Cola 
2           Pepsi     
3           Sun       
4           Oracle   
</pre>

<p>
In SQLite if a column is INTEGER and PRIMARY KEY, it is also auto-incremented.
</p>


<h2>Foreign key</h2>

<p>
A FOREIGN KEY in one table points to a PRIMARY KEY in another table. 
It is a referential constraint between two tables. The foreign key identifies a 
column or a set of columns in one (referencing) table that refers to a column or 
set of columns in another (referenced) table.
</p>

<p>
We will be demonstrating this constraint on two tables. Authors and Books.
</p>

<pre class="code">
CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
</pre>

<p>
Here we create the Authors table.
</p>


<pre class="code">
CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, 
    FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));
</pre>

<p>
We create the Books table. Here we have an AuthorId column name, which 
acts as a foreign key. It references to the primary key of the Authors table. 
</p>

<p>
In SQLite, foreign keys are not enforced by default. To enforce the foreign key, 
the library must be compiled
with proper flags, it must be at least version 3.6.19 and the pragma key
for foreign keys must be set. 
</p>

<p>
What would foreign key enforcement mean in our example? We could not insert a
row into the Books table with an AuthorId, which is not present in Authors book.
</p>


<h2>Check constraint</h2>

<p>
A CHECK constraint is a condition that defines valid data of a relational database.  
The check is executed when adding or updating data to the column in question.
</p>

<pre class="code">
sqlite> .schema Orders
CREATE TABLE Orders(Id INTEGER PRIMARY KEY, OrderPrice INTEGER CHECK(OrderPrice>0), 
Customer TEXT);
</pre>

<p>
We look at the definition of the Orders table. We see a CHECK constraint imposed
on the OrderPrice column. Logically, the price of an order must be a positive value. 
</p>

<pre class="code">
sqlite> INSERT INTO Orders(OrderPrice, Customer) VALUES(-10, 'Johnson');
Error: constraint failed
</pre>

<p>
If we try to insert an invalid value, we get an error saying: 'constraint failed'.
</p>


<h2>Default constraint</h2>

<p>
The DEFAULT constraint inserts a default value into the column, if
no value is available.
</p>

<pre class="code">
sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT, 
   ...> City TEXT DEFAULT 'not available');
</pre>

<p>
To demonstrate the DEFAULT constraint, we create a Hotels table. The City 
column has a default 'not available' value. 
</p>


<pre class="code">
sqlite> INSERT INTO Hotels(Name, City) VALUES('Kyjev', 'Bratislava');
sqlite> INSERT INTO Hotels(Name) VALUES('Slovan');
sqlite> .width 3 8 17
sqlite> SELECT * FROM Hotels;
Id   Name      City             
---  --------  -----------------
1    Kyjev     Bratislava       
2    Slovan    not available 
</pre>

<p>
In the first statement we provide both the hotel name and the city name. 
In the second statement, we provide only the hotel name. SQLite puts
the default value there, the 'not available' text. 
</p>


<p>
In this part of the SQLite tutorial, we have covered constraints supported
by SQLite database.
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 8, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

